/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.Projeto;

/**
 * Document : ProjetoDAO
 *
 * Created on : 11/03/2013, 14:44:34
 *
 * Author : Fiúza <rbfiuza@gmail.com>
 */
public class ProjetoDAO {

    Connection conn = null;
    PreparedStatement stmt = null;
    String sql = null;

    public Projeto getProjeto(int id) {
        Projeto projeto = null;
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "mysql");

            sql = "Select"
                    + " id"
                    + ",titulo"
                    + ",data_inicio"
                    + ",data_fim"
                    + " from projeto where id  = ?";

            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {

                projeto = new Projeto();
                projeto.setId(rs.getInt("id"));
                projeto.setDataInicio(rs.getString("data_inicio"));
                projeto.setDataFim(rs.getString("data_fim"));
                projeto.setTitulo(rs.getString("titulo"));

            }

            rs.close();
            stmt.close();
            stmt = null;


            conn.close();
            conn = null;

        } catch (Exception e) {
            System.out.println(e);
        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here           
                }

                stmt = null;
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here     
                }

                conn = null;
            }
        }

        return projeto;
    }

    public ArrayList<Projeto> getProjeto() {
        ArrayList<Projeto> projetoList = new ArrayList<Projeto>();

        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "mysql");

            sql = "Select"
                    + " id"
                    + ",titulo"
                    + ",data_inicio"
                    + ",data_fim"
                    + " from projeto where 1  = ?";

            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, 1);

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {

                Projeto projeto = new Projeto();
                projeto.setId(rs.getInt("id"));
                projeto.setDataInicio(rs.getString("data_inicio"));
                projeto.setDataFim(rs.getString("data_fim"));
                projeto.setTitulo(rs.getString("titulo"));

                projetoList.add(projeto);
            }

            rs.close();
            stmt.close();
            stmt = null;


            conn.close();
            conn = null;

        } catch (Exception e) {
            System.out.println(e);
        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here           
                }

                stmt = null;
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here     
                }

                conn = null;
            }
        }

        return projetoList;
    }

    public String insertProjeto() {
        String userId = null;

        return userId;
    }

    public boolean updateProjeto(String storeId, Projeto projeto) {
        boolean success = true;

        try {

            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "mysql");

            sql = "UPDATE projeto SET "
                    + "id = ?"
                    + ",titulo = ?"
                    + ",data_inicio= ?"
                    + ",data_fim = ?"
                    + " WHERE id = ?";

            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, projeto.getId());
            stmt.setString(2, projeto.getTitulo());
            stmt.setString(3, projeto.getDataInicio());
            stmt.setString(4, projeto.getDataFim());
            stmt.setInt(5, projeto.getId());

            stmt.executeUpdate();

            stmt.close();
            stmt = null;
            conn.close();
            conn = null;

        } catch (Exception e) {

            success = false;
            System.out.println(e);

        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here           
                }

                stmt = null;
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here     
                }

                conn = null;
            }
        }

        return success;
    }
}